package com.ysh.xiaomi.dao.impl;

import com.ysh.xiaomi.dao.GoodsDao;
import com.ysh.xiaomi.pojo.PageBean;
import com.ysh.xiaomi.pojo.TbGoods;
import com.ysh.xiaomi.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 袁胜虎 on 2022/8/17 19:51
 */
public class GoodsDaoImpl implements GoodsDao {
    @Override
    public List<TbGoods> findByType(Long typeId, PageBean pageBean) {
        //获取到连接
        QueryRunner qr = new QueryRunner();
        //计算limit的偏移量
        int pyl = (pageBean.getCurrentPage() - 1) * pageBean.getPageSize();
        //我们要执行的sql
        String sql = "SELECT tg.*, tgz.name,tgz.pubdate,tgz.intro,tgz.picture,tgz.price,tgz.star,tgz.typeid  from tb_goods tg,tb_goods_kz tgz where tg.kzid=tgz.id and tgz.typeid=? limit ?,?";
        //查询总条数的sql
        String countSql = "select COUNT(0) totalSize from tb_goods tg,tb_goods_kz tgz where tg.kzid=tgz.id and tgz.typeid=?";
        List<TbGoods> goodsList = null;
        try {
            //指定sql获取到返回的结果
            goodsList = qr.query(DbUtils.getConn(),sql, new BeanListHandler<>(TbGoods.class), typeId, pyl, pageBean.getPageSize());
            //获取到当前分类的商品的数量
            Map<String, Object> resultMap = qr.query(DbUtils.getConn(),countSql, new MapHandler(), typeId);
            //设置当前查询到的集合数据
            pageBean.setList(goodsList);
            //设置总条数, 总条数就是我们第二个sql的返回结果,通过totalSize作为key保存在map中
            pageBean.setTotalSize((Long) resultMap.get("totalSize"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goodsList;
    }

    @Override
    public TbGoods findById(Long id) {
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT tg.*, tgz.name,tgz.pubdate,tgz.intro,tgz.picture,tgz.price,tgz.star,tgz.typeid  from tb_goods tg,tb_goods_kz tgz where tg.kzid=tgz.id and tg.id=?";
        try {
            TbGoods goods = qr.query(DbUtils.getConn(), sql, new BeanHandler<>(TbGoods.class), id);
            return goods;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<TbGoods> findByIds(List ids) {
        //StringBuffer，是可以存储和操作字符串，即包含多个字符的字符串数据。
        StringBuffer stringBuffer = new StringBuffer();
        //根据条件的个数拼接?
        for (int i = 0;i <ids.size(); i++) {
            stringBuffer.append("?");
            if (i != ids.size()-1){
                //只要不是最后一个条件,就拼接
                stringBuffer.append(",");
            }
        }
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT tg.*, tgz.name,tgz.pubdate,tgz.intro,tgz.picture,tgz.price,tgz.star,tgz.typeid  from tb_goods tg,tb_goods_kz tgz where tg.kzid=tgz.id and tg.id in("+stringBuffer.toString()+")";
        try {
            List<TbGoods> goodsList = qr.query(DbUtils.getConn(), sql, new BeanListHandler<>(TbGoods.class), ids.toArray());
            return goodsList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addGoods(TbGoods tbGoods) {
        QueryRunner qr = new QueryRunner();

        String maxIdSql = "SELECT MAX(ID) + 1 FROM tb_goods_type";
        Long maxid = null;
        try {
            maxid=(Long) qr.query(DbUtils.getConn(),maxIdSql, new ScalarHandler());
        } catch (SQLException e) {
            e.printStackTrace();
        }


        String sql = "INSERT INTO tb_goods_type VALUE(?, ?, ?, ?, ?)";
        int count = 0;
        try{

            count = qr.update(DbUtils.getConn(),sql, new Object[]{maxid, tbGoods.getName(), tbGoods.getStar(), tbGoods.getPrice(), "1"});
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
